﻿/*****************************************************************************/
/* Stored Procedure :: ServiceMembersMatchingLastName                        */
/*****************************************************************************/
CREATE PROCEDURE dbo.ServiceMembersMatchingLastName
( 
	@LastName varchar( 128 ) 
)
AS  
BEGIN
SELECT	sm.OID as 'ServiceMenOID', 
		sm.Version as 'ServiceMenVersion', 
		sm.PrimaryAddressID as 'ServiceMenPrimaryAddressID', 
		sm.PermanentAddressID as 'ServiceMenPermanentAddressID',

		People.OID as 'PeopleOID', 
		People.Version as 'PeopleVersion', 
		People.FirstName, 
		People.LastName, 
		People.MiddleInitial, 
		People.DOB, 
		People.Sex, 
		People.Phone1, 
		People.Phone2, 
		People.eMailAddress,

		sh.OID			as 'ServiceHistoryOID',
		sh.Version		as 'ServiceHistoryVersion',
		sh.StartDate,
		sh.EndDate,
		sh.UnitAssignment,

		sh.TypeOfDischargeID,
		dis.Description as 'DischargeType',

		sh.BranchOfServiceID,
		br.Version as 'BranchVersion',
		br.Branch,

		sh.RankID,
		mr.Version as 'RankVersion',
		mr.RankStructure,
		mr.Rank,
		mr.PayGrade as 'RankPayGrade',
		mr.Abbreviation as 'RankAbbreviation'
	FROM ServiceMen sm
	INNER JOIN People On sm.OID = People.OID
    LEFT OUTER JOIN dbo.ServiceHistory sh ON sm.OID = sh.PersonnelID
    AND sh.Version = (SELECT MAX(Version) FROM dbo.ServiceHistory
					WHERE PersonnelID = sm.OID)
    LEFT OUTER JOIN dbo.TypesOfMilitaryBranch br ON sh.BranchOfServiceID = br.OID
    LEFT OUTER JOIN dbo.MilitaryRanks mr ON sh.RankID = mr.OID
    LEFT OUTER JOIN dbo.TypesOfDischarge dis ON sh.TypeOfDischargeID = dis.OID
	WHERE (People.LastName like @LastName)

END


